Attribute VB_Name = "MODweb"
' Public variables ---------------------------------------------------------------------------------------------

Public _
    initSQLStr As String, _
    finalSQLStr As String
    
Public _
    isNewRecord As Boolean, _
    bUpdatedFromFile As Boolean

'Objects -------------------------------------------------------------------------------------------------------

Private fso As Scripting.FileSystemObject
Private oSQL As Scripting.TextStream
Private oLog As Scripting.TextStream
Private oCLS_IE As CLS_IE

' User defined objects ---------------------------------------------------------------------------------------------------

Private Type SQLstatementList
   uniqueID As String
   SQLstatement As String
End Type

Private statementArray() As SQLstatementList

Private iArrayLength As Integer

' Constants -------------------------------------------------------------------------------------------------------------------------------

Private Const sUPDATE_DIR_NAME As String = "update"

' Booleans --------------------------------------------------------------------------------------------------------------------------------

Private bFatalError As Boolean

' Strings -------------------------------------------------------------------------------------------------------------------------------

Private _
    SQLclause As String, _
    sActionOnError As String, _
    statementListString As String

' Init Web Module ---------------------------------------------------------------------------------------------------------------------

Public Sub init()

    resetStatementArray

End Sub

Public Sub updateAllRecords(Optional sSQLin As String)

    On Error Resume Next

    logMessage _
        iDEBUG, _
        "Web", _
        "updateAllRecords method called "

    Dim _
        sSQL As String, _
        sTableName As String, _
        sPostData As String, _
        sURL As String
    
    If sSQLin = "" Then
    
        If iArrayLength < 1 Then ' In case there are no items in the SQLstatement list
        
            MsgBox "There are no edited records to update"
            
            Exit Sub
        
        End If
    
        sSQL = buildStatementListString
        
        logMessage _
            iDEBUG, _
            "Web", _
            "buildStatementListString method called ", _
            "Sql = " & sSQL
    
    Else
    
        sSQL = sSQLin
    
    End If
    
   ' Show modal 'updating' dialog --------------------------------------------------------------------------------
    
    If IsNumeric(FRMmain.LISTtables.ListIndex) And FRMmain.LISTtables.ListIndex > -1 Then
    
        sTableName = FRMmain.LISTtables.List(FRMmain.LISTtables.ListIndex)
        
    Else
    
        sTableName = "components"
        
    End If
    
    sPostData = "SQLstatementList=" & sSQL
    
    sPostData = Replace(sPostData, " ", "%20")
    sPostData = Replace(sPostData, "&", "%26")
    sPostData = sPostData & "&action=multipleUpdate"
    sPostData = sPostData & "&tableName=" & sTableName

    sURL = getPref("remoteDB", "serverAddress") & "/" & getPref("remoteDB", "updatePagePath")
    
    ' Send data to web browser for update -------------------------------------------------------------------------------------------
    
    Set oCLS_IE = New CLS_IE
    
    oCLS_IE.updateWithBrowser sURL, sPostData

    FRMmain.bRemoteSaved = True
    
End Sub

Public Function getPostData(sData As String)

    On Error Resume Next

    Dim aBuffer() As Byte, sBuffer$

    sBuffer$ = sData

    aBuffer = StrConv(sBuffer$, vbFromUnicode)
    
    getPostData = aBuffer

End Function

Public Function buildStatementListString()

    On Error Resume Next

    statementListString = ""
    
    For i = 0 To iArrayLength - 1
         statementListString = _
            statementListString & _
            statementArray(i).uniqueID & _
            ";;" & _
            statementArray(i).SQLstatement & _
            "||"
    Next
    
   ' Remove the last two 'pipes' ------------------------------------------------------------------------------------------------
    
    buildStatementListString = Left(statementListString, Len(statementListString) - 2)
    
End Function


Public Sub addSQLlistItem(uniqueID As String, SQLstatement As String)

    On Error Resume Next
   
    ' If the unique ID exists in the array, then replace its SQL statement with the new one, unless the existing SQLstatment operation is 'INSERT INTO' ---------------------------------------------

    For i = 1 To iArrayLength
        
        If statementArray(i - 1).uniqueID = uniqueID And _
            UCase(Left(statementArray(i - 1).SQLstatement, 11)) <> "INSERT INTO" Then
        
             statementArray(i - 1).SQLstatement = SQLstatement
             
             Exit Sub
             
        End If
        
    Next

    ReDim Preserve statementArray(iArrayLength)

    statementArray(iArrayLength).uniqueID = uniqueID
    statementArray(iArrayLength).SQLstatement = SQLstatement
    
    iArrayLength = iArrayLength + 1

    FRMmain.WebUpdateAllRecords.Enabled = True
    FRMmain.WebUpdateLater.Enabled = True
    FRMmain.editUpdateRow.Enabled = True
    
End Sub

Public Sub resetStatementArray()

    ReDim statementArray(0)
    iArrayLength = 0

End Sub

Public Function buildUpdateStatement(objRecordset As Recordset, clause As String)

    On Error Resume Next
    
    ' ths variable is checked before the user exits to make sure he updated the remote database
    ' or saved the list of records to update.
    ' It is set to true, if the database is updated, or if the records are saved
    
    FRMmain.bRemoteSaved = False
    
    SQLclause = clause
    
    If objRecordset.EOF Then Exit Function

    remoteDBfieldNames = MODpreferences.getPref("remoteDB", "fieldNames")

    remoteDBfieldNamesArray = Split(remoteDBfieldNames, ",")
    
    Select Case clause
        Case "WHERE"
            initSQLStr = " WHERE [uniqueID] = '" & objRecordset("uniqueID") & "'"
        
'            For i = 1 To UBound(remoteDBfieldNamesArray) - 1
'                initSQLStr = initSQLStr & " AND [" & remoteDBfieldNamesArray(i) & "] = '" & _
'                    objRecordset(remoteDBfieldNamesArray(i)) & "'"
'            Next
            
        Case "SET"
            finalSQLStr = "UPDATE " & FRMmain.LISTtables.List(FRMmain.LISTtables.ListIndex) & "" & _
                " SET [" & remoteDBfieldNamesArray(0) & "] = '" & objRecordset(remoteDBfieldNamesArray(0)) & "'"
            
            For i = 1 To UBound(remoteDBfieldNamesArray)
                finalSQLStr = finalSQLStr & " , [" & remoteDBfieldNamesArray(i) & "] = '" & _
                    objRecordset(remoteDBfieldNamesArray(i)) & "'"
            Next
            
            finalSQLStr = finalSQLStr & initSQLStr
            
            addSQLlistItem objRecordset("uniqueID"), finalSQLStr

        Case "INSERT"
        
            For i = 0 To UBound(remoteDBfieldNamesArray)
                sFieldNames = sFieldNames & remoteDBfieldNamesArray(i) & ","
            Next
        
            sFieldNames = Left(sFieldNames, Len(sFieldNames) - 1)
        
            For i = 0 To UBound(remoteDBfieldNamesArray)
                sFieldValues = sFieldValues & "'" & objRecordset(remoteDBfieldNamesArray(i)) & "',"
            Next
        
            sFieldValues = Left(sFieldValues, Len(sFieldValues) - 1)
            
            finalSQLStr = "INSERT INTO " & _
                FRMmain.LISTtables.List(FRMmain.LISTtables.ListIndex) & _
                " ( " & _
                sFieldNames & _
                " ) VALUES ( " & _
                sFieldValues & _
                " ) "
    
            '------------------------------------------------------------------------------
            ' Only check the autoUpdate preference if it is a new record,
            ' because if it is not a new row then the user must choose to
            ' update the remote record manually by pressing "OK" on the confirm dialog
            '------------------------------------------------------------------------------
            
            If isNewRecord Then

                addSQLlistItem objRecordset("uniqueID"), finalSQLStr
                
                ' Records are automatically updated after insertion because otherwise once the user edits the new row, the statement will be built with a 'WHERE' clause
                
                'updateAllRecords
                
            End If
                
        Case "DELETE"
            finalSQLStr = "DELETE * FROM " & _
                FRMmain.LISTtables.List(FRMmain.LISTtables.ListIndex) & _
                " WHERE " & _
                "uniqueID = '" & _
                objRecordset("uniqueID") & _
                "'"

            addSQLlistItem objRecordset("uniqueID"), finalSQLStr
            
    End Select
    
    
End Function

' Update remote database with SQL staments that were written to a text file -------------------------------------------------------------

Public Sub updateFromFile()

    On Error Resume Next

    bUpdatedFromFile = True

    Dim sSQL As String
    
    Set fso = New Scripting.FileSystemObject
    
    If Not fso.FileExists(MODweb.sRemoteUpdateRecordsFilePath) Then
    
        MsgBox "There is no file to read from"
        
        Exit Sub
    
    End If
    
    On Error Resume Next
    
    Set oSQL = fso.OpenTextFile(MODweb.sRemoteUpdateRecordsFilePath, ForReading, False)
    
    If Err.Number <> 0 Then
    
        MsgBox _
            "Could not open records file for updating." & _
            vbCrLf & _
            "Reason : " & _
            Err.Description
            
        logMessage _
            iERROR, _
            "Web", _
            "Could not open text file for reading records for update", _
            , _
            Err.Number, _
            Err.Description
        
        Exit Sub
    
    Else
    
        logMessage _
            iINFO, _
            "Web", _
            "Opened text file for reading records for update"
    
    End If
    
    On Error Resume Next
        
    sSQL = oSQL.ReadAll
    
    If Err.Number <> 0 Then
    
        MsgBox _
            "Could not read records file for updating." & _
            vbCrLf & _
            "Reason : " & _
            Err.Description
            
        logMessage _
            iERROR, _
            "Web", _
            "Could not read text file for update", _
            , _
            Err.Number, _
            Err.Description
        
        Exit Sub
    
    Else
    
        logMessage _
            iINFO, _
            "Web", _
            "Read text file for update", _
            "SQL = " & sSQL
    
    End If
    
    sSQL = Replace(sSQL, vbCrLf, "||")
    
    ' check for extra pipes and remove them -------------------------------------------------------------------------------
    
    If Right(sSQL, 2) = "||" Then sSQL = Left(sSQL, Len(sSQL) - 2)
    
    If Trim(sSQL) = "" Then
    
        MsgBox "File is empty"
        
        Exit Sub
        
    End If
    
    
    oSQL.Close
    
    updateAllRecords sSQL
    
End Sub

' Write SQl statements to a text file so the user can update the remote database at a later time --------------------------------------------------------

Public Sub exportRecordsForUpdate()

    On Error Resume Next

    Dim sSQL As String
    
    If iArrayLength = 0 Then ' In case there are no items in the SQLstatement list
        
        MsgBox "There are no edited records to update"
        
        Exit Sub
    
    End If
    
    sSQL = buildStatementListString
    
    sSQL = Replace(sSQL, "||", vbCrLf)
    
    If Trim(sSQL) = "" Then
        
        MsgBox "Could not save records for updating"
             
        Exit Sub
             
    End If
    
    Set fso = New Scripting.FileSystemObject
    
    If Not fso.FolderExists(sRemoteUpdateDirPath) Then fso.CreateFolder sRemoteUpdateDirPath
    
    On Error Resume Next
    
    Set oSQL = fso.OpenTextFile(MODweb.sRemoteUpdateRecordsFilePath, ForAppending, True)

    If Err.Number <> 0 Then
    
        MsgBox _
            "Could not open records file for saving." & _
            vbCrLf & _
            "Reason : " & _
            Err.Description
            
        logMessage _
            iERROR, _
            "Web", _
            "Could not open text file for saving records for update", _
            , _
            Err.Number, _
            Err.Description
        
        Exit Sub
    
    Else
    
        logMessage _
            iINFO, _
            "Web", _
            "Opened text file for saving records for update"
    
    End If
    
    ' Write SQL statements to text file ----------------------------------------------------------------------------------------------
      
    On Error Resume Next
      
    oSQL.Write sSQL & vbCrLf
    
     If Err.Number <> 0 Then
    
        MsgBox _
            "Could not write to save file." & _
            vbCrLf & _
            "Reason : " & _
            Err.Description
            
        logMessage _
            iERROR, _
            "Web", _
            "Could not write to text file for saving records", _
            "SQL = " & sSQL, _
            Err.Number, _
            Err.Description
        
        Exit Sub
    
    Else
    
        logMessage _
            iINFO, _
            "Web", _
            "Wrote records to text file for saving", _
            "SQL = " & sSQL
    
    End If
    
    oSQL.Close
    
    Set oSQL = Nothing
    
    Set fso = Nothing
    
    FRMmain.bRemoteSaved = True
    
    resetStatementArray
    
    MsgBox "Records saved"
    
End Sub

Public Property Get sRemoteUpdateDirPath() As String

    sRemoteUpdateDirPath = _
        App.Path & "/" & _
        sUPDATE_DIR_NAME & _
        "/"

End Property

Public Property Get sRemoteUpdateRecordsFilePath() As String
    
    sRemoteUpdateRecordsFilePath = _
        App.Path & _
        "/" & _
        sUPDATE_DIR_NAME & _
        "/webSQL.update"

End Property

Public Property Get sRemoteUpdateBackupDirPath() As String
    
    sRemoteUpdateBackupDirPath = _
        App.Path & _
        "/" & _
        sUPDATE_DIR_NAME & _
        "/" & _
        "backup" & _
        "/"

End Property


